{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SF Open Data Portal\n",
    "\n",
    "Browse the portal for public datasets: https://data.sfgov.org/\n",
    "\n",
    "Use Python to retrieve data from the API, just like we learned last week: https://data.sfgov.org/developers\n",
    "\n",
    "Look at data on bike parking in SF: https://data.sfgov.org/Transportation/Bicycle-Parking-Public-/2e7e-i7me"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd, requests, json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# use endpoint for bike parking in SF\n",
    "endpoint_url = 'https://data.sfgov.org/resource/dd7x-3h4a.json'\n",
    "\n",
    "# open a connection to the URL and download its response\n",
    "response = requests.get(endpoint_url)\n",
    "\n",
    "# parse the json string into a Python dict\n",
    "data = response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'address': '4634 03RD ST',\n",
       " 'location': 'VeloBrews Cafe & Cycling Community Center',\n",
       " 'month_installed': '12',\n",
       " 'number_of_racks': '5',\n",
       " 'number_of_spaces': '10',\n",
       " 'object_id': '37',\n",
       " 'placement': 'ROADWAY',\n",
       " 'street': '03RD ST',\n",
       " 'year_installed': '2014'}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>address</th>\n",
       "      <th>geom</th>\n",
       "      <th>location</th>\n",
       "      <th>month_installed</th>\n",
       "      <th>number_of_racks</th>\n",
       "      <th>number_of_spaces</th>\n",
       "      <th>object_id</th>\n",
       "      <th>placement</th>\n",
       "      <th>street</th>\n",
       "      <th>year_installed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4634 03RD ST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VeloBrews Cafe &amp; Cycling Community Center</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>37</td>\n",
       "      <td>ROADWAY</td>\n",
       "      <td>03RD ST</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023 MISSION ST</td>\n",
       "      <td>{'coordinates': [-122.419104, 37.764429], 'typ...</td>\n",
       "      <td>Mission Plaza</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>438</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>MISSION</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70 04TH ST</td>\n",
       "      <td>{'coordinates': [-122.404896, 37.784708], 'typ...</td>\n",
       "      <td>Cole Hardware</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>853</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>04TH ST</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>714 MONTGOMERY ST</td>\n",
       "      <td>{'coordinates': [-122.403068, 37.795902], 'typ...</td>\n",
       "      <td>Bubble Lounge</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2407</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>MONTGOMERY</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1740 OFARRELL ST</td>\n",
       "      <td>{'coordinates': [-122.433316, 37.783481], 'typ...</td>\n",
       "      <td>Fat Angel Wine Bar</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>265</td>\n",
       "      <td>SIDEWALK</td>\n",
       "      <td>O'FARRELL</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             address                                               geom  \\\n",
       "0       4634 03RD ST                                                NaN   \n",
       "1    2023 MISSION ST  {'coordinates': [-122.419104, 37.764429], 'typ...   \n",
       "2         70 04TH ST  {'coordinates': [-122.404896, 37.784708], 'typ...   \n",
       "3  714 MONTGOMERY ST  {'coordinates': [-122.403068, 37.795902], 'typ...   \n",
       "4   1740 OFARRELL ST  {'coordinates': [-122.433316, 37.783481], 'typ...   \n",
       "\n",
       "                                    location month_installed number_of_racks  \\\n",
       "0  VeloBrews Cafe & Cycling Community Center              12               5   \n",
       "1                              Mission Plaza               0               1   \n",
       "2                              Cole Hardware               7               2   \n",
       "3                              Bubble Lounge               9               1   \n",
       "4                         Fat Angel Wine Bar              11               1   \n",
       "\n",
       "  number_of_spaces object_id placement      street year_installed  \n",
       "0               10        37   ROADWAY     03RD ST           2014  \n",
       "1                2       438  SIDEWALK     MISSION           2002  \n",
       "2                4       853  SIDEWALK     04TH ST           2015  \n",
       "3                2      2407  SIDEWALK  MONTGOMERY           2010  \n",
       "4                2       265  SIDEWALK   O'FARRELL           2010  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# turn the json data into a dataframe\n",
    "df = pd.DataFrame(data)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'coordinates': [-122.419104, 37.764429], 'type': 'Point'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this column contains dicts that contain lats and lons\n",
    "df['geom'][1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create lists to hold all my lat-lons as i extract them\n",
    "latitudes = []\n",
    "longitudes = []\n",
    "\n",
    "# loop through each row in df, extracting lat and lon values if geom is not null\n",
    "for label, row in df.iterrows():\n",
    "    if pd.notnull(row['geom']):\n",
    "        latitudes.append(row['geom']['coordinates'][1])\n",
    "        longitudes.append(row['geom']['coordinates'][0])\n",
    "    else:\n",
    "        latitudes.append(None)\n",
    "        longitudes.append(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['lat'] = latitudes\n",
    "df['lon'] = longitudes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37.764429</td>\n",
       "      <td>-122.419104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37.784708</td>\n",
       "      <td>-122.404896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37.795902</td>\n",
       "      <td>-122.403068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37.783481</td>\n",
       "      <td>-122.433316</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         lat         lon\n",
       "0        NaN         NaN\n",
       "1  37.764429 -122.419104\n",
       "2  37.784708 -122.404896\n",
       "3  37.795902 -122.403068\n",
       "4  37.783481 -122.433316"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df[['lat', 'lon']]\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
